package staff.io;

import static staff.util.DateUtils.getDateFormat;
import static staff.util.DateUtils.setDateFormat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import staff.logic.Employee;

/** Class for saving employes to MySQL database. */
public class MySQLEmployeSaver implements EmployeSaver {
    private static Connection connection;
    private static String EMPLOYE_TABLE = "employes";
    public static final String MYSQL_DATE_FORMAT = "yyyy.MM.dd";
    private String savedDateFormat;

    /** Constructor */
    public MySQLEmployeSaver(String connectString, String user, String password)
            throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql:" + connectString;
        connection = DriverManager.getConnection(url, user, password);
        prepareEmployeTable();
        // MySQL uses another date format
        savedDateFormat = getDateFormat(); // save original date format
        setDateFormat(MYSQL_DATE_FORMAT); // set MySQL format
    }

    /** Save next employe */
    public void saveNext(Employee employe) {
        StringBuilder insertEmployeSql = new StringBuilder();
        insertEmployeSql.append("INSERT INTO " + EMPLOYE_TABLE + " (");
        int fieldsCount = Employee.FieldTag.values().length;
        // append fields
        for (int fieldNum = 0; fieldNum < fieldsCount; fieldNum++) {
            Employee.FieldTag fieldTag = Employee.FieldTag.values()[fieldNum];
            insertEmployeSql.append(fieldTag.toString());
            if (fieldNum < fieldsCount - 1) {
                insertEmployeSql.append(", ");
            }
        }
        insertEmployeSql.append(") VALUES (");
        // append values
        for (int fieldNum = 0; fieldNum < fieldsCount; fieldNum++) {
            Employee.FieldTag fieldTag = Employee.FieldTag.values()[fieldNum];
            insertEmployeSql.append("'"
                    + employe.getParamByName(fieldTag.toString()) + "'");
            if (fieldNum < fieldsCount - 1) {
                insertEmployeSql.append(", ");
            }
        }
        insertEmployeSql.append(")");
        // execute insert
        try {
            executeSQL(connection, insertEmployeSql.toString());
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    /** Close employe saver */
    public void close() {
        setDateFormat(savedDateFormat); // return original date format
        try {
            connection.close();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    /** Execute sql string */
    private static void executeSQL(Connection connection, String sql)
            throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.execute();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    private void prepareEmployeTable() throws SQLException {
        executeSQL(connection, "DROP TABLE IF EXISTS " + EMPLOYE_TABLE);
        createEmployeTable();
    }

    private void createEmployeTable() throws SQLException {
        StringBuilder createTableSql = new StringBuilder();
        createTableSql.append("CREATE TABLE " + EMPLOYE_TABLE + " (");
        for (Employee.FieldTag tag : Employee.FieldTag.values()) {
            createTableSql.append(getFieldMetaString(tag));
            createTableSql.append(", ");
        }
        createTableSql.append("primary key (employeId))");
        executeSQL(connection, createTableSql.toString());
    }

    private String getFieldMetaString(Employee.FieldTag fieldTag) {
        switch (fieldTag) {
        case fullName:
            return "fullName varchar(255)";
        case birthDate:
            return "birthDate date";
        case hireDate:
            return "hireDate date";
        case position:
            return "position varchar(25)";
        case description:
            return "description varchar(255)";
        case employeId:
            return "employeId int unsigned not null auto_increment";
        case managerId:
            return "managerId int unsigned";
        default:
            assert false;
        }
        throw new IllegalStateException(
                "Field tag not found in switch statement.");
    }

}
